package org.judo.admin;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.judo.components.state.SessionScope;
import org.judo.components.uicontrol.ChoiceControl;
import org.judo.components.uicontrol.validation.Required;
import org.judo.controller.PageController;
import org.judo.database.JudoDBConnection;
import org.judo.database.JudoDatabaseManager;
import org.judo.database.orm.mappers.SimpleJDBCConnection;
import org.judo.database.profile.ColumnSchema;
import org.judo.database.profile.EntityProfiler;
import org.judo.database.profile.TableSchema;
import org.judo.properties.Props;
import org.judo.propertyobject.Prop;
import org.judo.propertyobject.ValidationError;
import org.judo.util.Util;

@SessionScope
public class DatabaseAdmin extends PageController
{
	@Prop List databaseNames = new ArrayList(); 
	@Prop ChoiceControl dbNames = new ChoiceControl(); 
	
	@Required
	@Prop String sql;
	
	@Prop String result;
	@Prop String type;
	
	@Prop String tableDetails;
	
	public void componentCreated()
	{
		String dbs = Props.get("judo.admin.dbs");
		String dbList[] = dbs.split(",");
		
		dbNames.addOption("NA","Select a Database");
		
		for (String name:dbList)
		{
			if (name.trim().equals(""))
				continue;
	
			dbNames.addOption(name,name);
		}
	}
	
	public void dbAdmin()
	{
	}
	
	public void showSchema()
	{
	}
	
	public void defineSchema()
		throws Exception
	{
		result = "";
		String db = (String)dbNames.value();
		
	//	if (isUnsellected(db))
	//		throw new ValidationError("Please select a database");

		JudoDBConnection con = null;
		ResultSet rs = null;
		Statement stmnt = null;
		Connection connection = null;
		
		try
		{
			tableDetails = "<ul>\n";
			
			con = JudoDatabaseManager.getConnection(db);			
			connection = (Connection)con.getConnectionService();
			TableSchema tables[] = EntityProfiler.allTableNames(connection);

			for (TableSchema table:tables)
			{
				tableDetails += "<li>"+table.tableName+"\n";
				tableDetails += "<ul>\n";
				
				for (ColumnSchema col:table.columnList)
				{
					tableDetails += "<li>"+col.name+", Java Type: "+col.type+"\n";
				}
				
				tableDetails += "</ul>\n";
			}
			
			tableDetails += "</ul>\n";
		}
		catch(Exception e)
		{
			connection.rollback();
			e.printStackTrace();
			tableDetails = "Error Getting Table Schema: "+e.getMessage();
		}
		finally
		{
			if (rs != null)
				rs.close();
			
			if (stmnt != null)
				stmnt.close();
			
			if (con != null)
			{
				con.commitTransaction();
				con.closeConnection();
			}
		}
		
		showPage("/system/database_admin/show_schema.do");
	}
	
	public void runSql()
		throws Exception
	{
		result = "";
		String db = (String)dbNames.value();
		
		if (isUnsellected(db))
			throw new ValidationError("Please select a database");

		JudoDBConnection con = null;
		ResultSet rs = null;
		Statement stmnt = null;
		Connection connection = null;
		
		try
		{
			con = JudoDatabaseManager.getConnection(db);
			connection = (Connection)con.getConnectionService();
			
			stmnt = connection.createStatement();
			stmnt.execute(sql);
			rs  = stmnt.getResultSet();
			
			if (rs == null)
			{
				type = "update";
				result = "SQL Executed";
			}
			else
			{
				result = "<table class=\"standardTBL\">\n";
				
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				
				result += "<tr class=\"standardTBLHeader\">\n";
				for (int i=0;i<count;i++)
				{
					result += "<td>\n";
					result += "<strong>"+metaData.getColumnName(i+1)+"</strong>";
					result += "</td>\n";
				}
				
				result += "</tr>\n";
				
				while (rs.next())
				{
					result += "<tr>\n";
					for (int i=0;i<count;i++)
					{
						result += "<td>\n";
						result += rs.getString(i+1)+"\n";
						result += "</td>\n";
					}
					result += "</tr>\n";
				}
				
				result += "</table>\n";
			}
			
			con.commitTransaction();
		}
		catch(SQLException e)
		{
			connection.rollback();
			result = "Could not execute SQL: "+e.getMessage();
		}
		finally
		{
			if (rs != null)
				rs.close();
			
			if (stmnt != null)
				stmnt.close();
			
			if (con != null)
				con.closeConnection();	
		}
		
		showPage("/system/database_admin/db_admin.do");
	}
}
